﻿using STHPMS1.DAL.D_Core;
using STHPMS1.ICore.I_DAL.Repository;
using STHPMS1.ICore.I_Entity.Stmpms1;
using System.Collections.Generic;
using System.Data;
using ServiceStack.OrmLite;
using System.Text;
using System.Data.SqlClient;
using System;
using System.Linq.Expressions;
using ServiceStack.OrmLite.SqlServer;

namespace STHPMS1.DAL.D_Repository
{
    public class S_InspeaccseRepository : DalBase, IS_InspeaccseRepository
    {

        public bool Exists(string unit, string inspecid, string tableName, string svydate)
        {
            string sql = string.Format("select count(unit) from {2} where UNIT='{0}' and  SVYDATA='{1}'", unit, svydate, tableName);
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                try
                {
                    IDbCommand cmmd = conn.CreateCommand();
                    cmmd.CommandText = sql.ToString();
                    object obj = cmmd.ExecuteScalar();
                    conn.Close();
                    return Convert.ToInt32(obj) > 0;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }


        public DataTable GetRdway(string tableName, string inspecid)
        {
            string sql = string.Format("select RDWAYID from {0} where INSPECID='{1}' group by RDWAYID",tableName,inspecid);
            return Adapter.DataAdapter.FillDataTable(sql);
        }

        public DataTable GetInspeaccseData(string rdid, string inspecid,int dir,string tableName)
        {
            string sql =string.Format(@"select SVYDATA,min(BEGINMP) MIN,max(ENDMP) MAX from {3} a inner join B_UNIT b
on a.unit = b.unit
 where a.RDWAYID='{0}' and INSPECID='{1}' and b.DIRECTION_DICTID= {2}
and a.SVYDATA = (Select MAX(svydata) from {3} where RDWAYID='{0}' and INSPECID='{1}' and DIRECTION_DICTID = {2})
group by svydata", rdid,inspecid,dir,tableName);           

            return Adapter.DataAdapter.FillDataTable(sql);
        }


        public bool UpdateStatus(string inspecId,string typeColumn)
        {
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                string sql = string.Format("update S_INSPEACCSE set {0}=1 where INSPECID='{1}'",typeColumn,inspecId);
                return conn.ExecuteNonQuery(sql) > 0;
            }
        }

        public IEnumerable<T> GetInspeaccsePerYear<T>(string rdwaryId)
            where T : class, IS_Inspeaccse
        {
            string sql =string.Format(@"select * from (select * , row_number() over(partition by RDWAYID,[YEAR] order by [INSPECID] desc) as sequence 
from S_INSPEACCSE where RDWAYID='{0}' and ISACCESS=1) a where sequence =1 ORDER BY [YEAR] asc ;", rdwaryId);
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Select<T>(sql);
        }

        public void UpdateUnit(string tableName, List<DataRow> olds, string inspecid)
        {
            //更新原有数据
            StringBuilder sql = new StringBuilder();
            //路基
            if(tableName.ToUpper()=="S_SUBGRD")
            {
                foreach(DataRow dr in olds)
                {
                sql.AppendFormat(@"update S_SUBGRD set NAME='{0}', SLDDG_L='{1}', SVYPS='{2}', PMWID='{3}', SLDDG_H='{4}', 
SLDDHIM='{5}', SLPCP_L='{6}', SLPCP_M='{7}', SLPCP_H='{8}', WATGUY_L='{9}', WATGUY_M='{10}', 
WATGUY_H='{11}', DASUGCT_L='{12}', DASUGCT_M='{13}', DASUGCT_H='{14}', CUBDAMG='{15}', 
SUBSLT_L='{16}', SUBSLT_M='{17}', SUBSLT_H='{18}', DRSYST_L='{19}', DRSYST_H='{20}', IMPORTDATE='{21}'
where UNIT='{22}' and SVYDATA='{23}' and INSPECID='{24}';",dr["NAME"],dr["SLDDG_L"],dr["SVYPS"],dr["PMWID"],dr["SLDDG_H"],dr["SLDDHIM"],dr["SLPCP_L"],dr["SLPCP_M"],dr["SLPCP_H"],
                                                          dr["WATGUY_L"],dr["WATGUY_M"],dr["WATGUY_H"],dr["DASUGCT_L"],dr["DASUGCT_M"],
                                                          dr["DASUGCT_H"],dr["CUBDAMG"],dr["SUBSLT_L"],dr["SUBSLT_M"],dr["SUBSLT_H"],
                                                          dr["DRSYST_L"],dr["DRSYST_H"],dr["IMPORTDATE"],dr["UNIT"],dr["SVYDATA"],dr["INSPECID"]);
                }
            }
            else if(tableName.ToUpper()=="S_RDDF") //沿线设施
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_RDDF set NAME='{0}', SVYPS='{1}', PMWID='{2}', PTFLDF_L='{3}', PTFLDF_H='{4}', 
ISGTDMG='{5}', MKDEFT='{6}', INDEFT='{7}', AFMNGPR='{8}', IMPORTDATE='{9}'
where UNIT='{10}' and SVYDATA='{11}' and INSPECID='{12}';", dr["NAME"], dr["SVYPS"], dr["PMWID"], dr["PTFLDF_L"], dr["PTFLDF_H"], dr["ISGTDMG"], dr["MKDEFT"], dr["INDEFT"], dr["AFMNGPR"], dr["IMPORTDATE"], dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }
            else if (tableName.ToUpper() == "S_RUTTING") //车辙
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_RUTTING set NAME='{0}', DEVICE='{1}', SURVEYER='{2}', RUT_LN1='{3}', IMPORTDATE='{4}', RUT_LN2='{5}', RD='{6}'
where UNIT='{7}' and SVYDATA='{8}' and INSPECID='{9}';",
                    dr["NAME"], dr["DEVICE"], dr["SURVEYER"], dr["RUT_LN1"],dr["IMPORTDATE"], dr["RUT_LN2"], dr["RD"],  dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }

            else if (tableName.ToUpper() == "S_BTSTRUT") //桥隧构造物
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_BTSTRUT set NAME='{0}', SVYPS='{1}', PMWID='{2}', BDTC_OT='{3}', BDTC_THR='{4}', BDTC_FU='{5}', BDTC_FV='{6}',
TUNL_N='{7}',TUNL_A='{8}',TUNL_D='{9}',CLVT_F='{10}',CLVT_P='{11}',CLVT_D='{12}',CLVT_LP='{13}',IMPORTDATE='{14}'
where UNIT='{15}' and SVYDATA='{16}' and INSPECID='{17}';",
                    dr["NAME"], dr["SVYPS"], dr["PMWID"], dr["BDTC_OT"], dr["BDTC_THR"], dr["BDTC_FU"], dr["BDTC_FV"],
                    dr["TUNL_N"], dr["TUNL_A"], dr["TUNL_D"], dr["CLVT_F"], dr["CLVT_P"], dr["CLVT_D"], dr["CLVT_LP"], dr["IMPORTDATE"],
                    dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }

            else if (tableName.ToUpper() == "S_RDFRC") //抗滑
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_RDFRC set NAME='{0}', DEVICE='{1}', SURVEYER='{2}', SFC='{3}', COMMENTS='{4}',IMPORTDATE='{5}'
where UNIT='{6}' and SVYDATA='{7}' and INSPECID='{8}';",
                    dr["NAME"], dr["DEVICE"], dr["SURVEYER"], dr["SFC"], dr["COMMENTS"],  dr["IMPORTDATE"],
                    dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }
            else if (tableName.ToUpper() == "S_RDRGH") //平整度
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_RDRGH set NAME='{0}', DEVICE='{1}', SURVEYER='{2}', IRI='{3}', COMMENTS='{4}',IMPORTDATE='{5}'
where UNIT='{6}' and SVYDATA='{7}' and INSPECID='{8}';",
                   dr["NAME"], dr["DEVICE"], dr["SURVEYER"], dr["IRI"], dr["COMMENTS"], dr["IMPORTDATE"],
                   dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }
            else if (tableName.ToUpper() == "S_RDPSSI") //强度
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_RDPSSI set NAME='{0}', DEVICE='{1}', SURVEYER='{2}', LFDEFL='{3}', RGDEFL='{4}', REPDEFL='{5}', SSI='{6}', COMMENTS='{7}',IMPORTDATE='{8}'
where UNIT='{9}' and SVYDATA='{10}' and INSPECID='{11}';",
                   dr["NAME"], dr["DEVICE"], dr["SURVEYER"], dr["LFDEFL"], dr["RGDEFL"], dr["REPDEFL"], dr["SSI"],  dr["COMMENTS"], dr["IMPORTDATE"],
                   dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }

            else if (tableName.ToUpper() == "S_PMDS_ASP")//沥青路面
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_PMDS_ASP set NAME='{0}',  SVYPS='{1}', PMWID='{2}', ALGCK_L='{3}', 
ALGCK_M='{4}', ALGCK_H='{5}', BLFT_L='{6}', BLFT_H='{7}', LGCK_L='{8}', LGCK_H='{9}', 
PIT_L='{10}', PIT_H='{11}', TRSCK_L='{12}', LOS_L='{13}', TRSCK_H='{14}', 
LOS_H='{15}', SINK_L='{16}', SINK_H='{17}', PLWV_L='{18}', PLWV_H='{19}',RUT_L='{20}',RUT_H='{21}',BLED='{22}',REPAIR='{23}', IMPORTDATE='{24}'
where UNIT='{25}' and SVYDATA='{26}' and INSPECID='{27}';", dr["NAME"], dr["SVYPS"], dr["PMWID"], dr["ALGCK_L"], dr["ALGCK_M"], dr["ALGCK_H"], dr["BLFT_L"], dr["BLFT_H"],
  dr["LGCK_L"], dr["LGCK_H"], dr["PIT_L"], dr["PIT_H"], dr["TRSCK_L"], dr["LOS_L"], dr["TRSCK_H"], dr["LOS_H"], dr["SINK_L"], dr["SINK_H"],
       dr["PLWV_L"], dr["PLWV_H"], dr["RUT_L"], dr["RUT_H"], dr["BLED"], dr["REPAIR"], dr["IMPORTDATE"], dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }
            else if (tableName.ToUpper() == "S_PMDS_S") //砂石路面
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_PMDS_S set NAME='{0}', PMWID='{1}', RDCBDC='{2}', SVYPS='{3}', RUT='{4}', PIT='{5}', BRFD='{6}',IMPORTDATE='{7}',SINK='{8}'
where UNIT='{9}' and SVYDATA='{10}' and INSPECID='{11}';",
                   dr["NAME"], dr["PMWID"], dr["RDCBDC"], dr["SVYPS"], dr["RUT"], dr["PIT"], dr["BRFD"], dr["IMPORTDATE"],dr["SINK"],
                   dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }
            else if (tableName.ToUpper() == "S_PMDS_CEM")//水泥路面
            {
                foreach (DataRow dr in olds)
                {
                    sql.AppendFormat(@"update S_PMDS_CEM set NAME='{0}',  SVYPS='{1}', PMWID='{2}', CRACK_L='{3}', 
CRACK_M='{4}', CRACK_H='{5}', CRSLB_H='{6}', PLCNCK_L='{7}', PLCNCK_M='{8}', PLCNCK_H='{9}', 
STGR_L='{10}', STGR_H='{11}', PUMP='{12}', CNSPL_L='{13}', CNSPL_M='{14}', 
CNSPL_H='{15}', JNSTDG_L='{16}', JNSTDG_H='{17}', PTHL='{18}', HUHUP='{19}',REPAIR='{20}', IMPORTDATE='{21}'
where UNIT='{22}' and SVYDATA='{23}' and INSPECID='{24}';", dr["NAME"], dr["SVYPS"], dr["PMWID"], dr["CRACK_L"], dr["CRACK_M"], dr["CRACK_H"], dr["CRSLB_H"], dr["PLCNCK_L"],
  dr["PLCNCK_M"], dr["PLCNCK_H"], dr["STGR_L"], dr["STGR_H"], dr["PUMP"], dr["CNSPL_L"], dr["CNSPL_M"], dr["CNSPL_H"], dr["JNSTDG_L"], dr["JNSTDG_H"],
       dr["PTHL"], dr["HUHUP"], dr["REPAIR"],  dr["IMPORTDATE"], dr["UNIT"], dr["SVYDATA"], dr["INSPECID"]);
                }
            }

            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                conn.Open();
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    IDbCommand cmmd = conn.CreateCommand();
                    cmmd.Transaction = tran;
                    cmmd.CommandText = sql.ToString();
                    cmmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }
    }
}
